declare handler

SQL/PL

Declare a condition or exception handler

Syntax

declare_handler:
          DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR condition_value_list sqlproc_stmt

condition_value_list:
          condition_value[, condition_value]...

condition_value:
          specific_condition | SQLEXCEPTION | SQLWARNING | NOT FOUND

specific_condition:
          cond_name
     |    FOR SQLSTATE [VALUE] "string"
     |    FOR SQLCODE [VALUE] {code_name | code_num}

Description

An exception handler is used to specify the set of actions to be performed whenever the exceptions (or signals) for the specified condition(s) are raised during the execution of statements within the handler's scope.

Three types of handlers can be declared:

A CONTINUE handler executes the statement(s) specified by sqlproc_stmt and then control continues at the next statement following the one which caused the exception to be raised.
An EXIT handler executes the statement(s) specified by sqlproc_stmt and then exits the block in which the handler was declared leaving in place any database modifications made by the statements that executed before the one which raised the exception.
An UNDO handler behaves the same as an EXIT handler except the changes made by the statements in the handler's block that executed before the one which raised the exception are rolled back.

The condition_value_list identifies the specific exception conditions that will be processed by the handler. Specific conditions are identified by:

a previously declared condition name (cond_name),
a specific SQLSTATE,
or a SQLCODE value.

General conditions are identified by:

SQLEXCEPTION,
SQLWARNING, or
NOT FOUND.

The SQLEXCEPTION handler is invoked for any error exception that is raised for which a specific handler does not already exist.

The SQLWARNING handler is invoked for any warning exception (e.g., data truncation) that is raised for which a specific handler does not already exist.

The NOT FOUND condition defines a handler for any statement that raises a not found condition.

You cannot mix a general condition in the same condition_value_list containing a specific condition.

Example

declare exit handler for not found
 	signal sqlcode eSQL_SIGNAL 
       set message_text = "book with that title not found";